#!/usr/bin/env python
# _*_coding:utf-8_*_
'''
shop.db
	- user / 6 items
    	id : username : passwd : false_num  : money : last_login 

	- shop / 5 items
    	id : product : price : type : owner

	- history / 6 items
    	id : username : buy_time : product_items : total_price
'''

import sqlite3
conn = sqlite3.connect('shop.db')
cursor = conn.cursor()
# 创建表
cursor.execute("create table user (id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(100), passwd varchar(100),false_num int(100), money int(200), last_login varchar(100))")
cursor.execute("create table shop (id INTEGER PRIMARY KEY AUTOINCREMENT, product varchar(100), price int(100), type int(100), owner int(100))")
cursor.execute("create table history (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(100), buy_time varchar(200), product_items varchar(65500), total_price varchar(100))")

# 向user表插入数据
cursor.execute("insert into user (name,passwd,false_num,money,last_login) values ('wang', '123', 0, 10000, '2015-05-06 11:11:11')")
cursor.execute("insert into user (name,passwd,false_num,money,last_login) values ('alex', 'abc', 0, 10000, '2015-05-16 12:12:12')")

# 向shop表插入数据
cursor.execute("insert into shop (product,price,type,owner) values ('电脑',0,1,0)")
cursor.execute("insert into shop (product,price,type,owner) values ('衣服',0,1,0)")
cursor.execute("insert into shop (product,price,type,owner) values ('手机',0,1,0)")
cursor.execute("insert into shop (product,price,type,owner) values ('Mac Pro', 9999, 2, 1)")
cursor.execute("insert into shop (product,price,type,owner) values ('Mac Air', 8500, 2, 1)")
cursor.execute("insert into shop (product,price,type,owner) values ('Thinkpad S2', 8500, 2, 1)")
cursor.execute("insert into shop (product,price,type,owner) values ('Polo shirt',200,2 ,2)")
cursor.execute("insert into shop (product,price,type,owner) values ('T-shirt', 50,2,2)")
cursor.execute("insert into shop (product,price,type,owner) values ('Iphone6S',5000,2,3)")
cursor.execute("insert into shop (product,price,type,owner) values ('OPPO R9',2668,2,3)")
cursor.execute("insert into shop (product,price,type,owner) values ('Vivo X6S',2499,2,3)")

# 向history表插入数据

# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭事务
conn.close()